package com.bjc.platform.util;

import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * excel导出
 * @author BJC
 */
public class ExcelExporter {
	
	public final static String TYPE_SN = "sn";  //序号serial number
	public final static String TYPE_TEXT = "text";
	public final static String TYPE_DATE = "date";
	public final static String TYPE_DATETIME = "datetime";
	public final static String TYPE_NUMBERIC = "numeric";
	public final static String TYPE_CODE = "code";
	
	/**
	 * MS EXCEL 97-2003格式输出流
	 * @param title 标题。null表示无标题行
	 * @param colDefs 列定义
	 * @param dataList 数据
	 * @param os 输出流
	 * @throws Exception
	 */
	public static void export(String title, List<ExcelColumnDefinition> colDefs, List<?> dataList, OutputStream os)
	throws Exception{
		
		if(colDefs == null || colDefs.size() == 0 || os == null){
			throw new Exception("Invalid parameters");
		}
		
		int cols = colDefs.size();
		int sheets = 0; //计数器——工作表数
		int rows = 0;   //计数器——打印行号
		int count = 0;  //计数器——打印条数
		int maxRows = 65536;  //每页最多输出行数
		
		//工作簿、字体及样式定义
		HSSFWorkbook wb = new HSSFWorkbook();
	  
	    // 定义字体样式
	    HSSFFont font = wb.createFont();
	    font.setFontName("宋体");
	    font.setFontHeightInPoints((short)10); //字体大小
	    font.setColor(HSSFColor.BLACK.index);
	    font.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL); //BOLDWEIGHT_BOLD
	    font.setItalic(false);
	    font.setUnderline(HSSFFont.U_NONE); //U_NONE, U_SINGLE, U_DOUBLE, U_SINGLE_ACCOUNTING, U_DOUBLE_ACCOUNTING
	    
	    HSSFFont fontColumnTitle = wb.createFont();
	    fontColumnTitle.setFontName("宋体");
	    fontColumnTitle.setFontHeightInPoints((short)12);
	    fontColumnTitle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

	    HSSFFont fontHeadline = wb.createFont();
	    fontHeadline.setFontName("宋体");
	    fontHeadline.setFontHeightInPoints((short)14);
	    fontHeadline.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
	    
	    // 定义单元格样式
	    HSSFCellStyle headlineStyle = createTitleCellStyle(wb, fontHeadline); //标题样式
	    HSSFCellStyle colTitleStyle = createTitleCellStyle(wb, fontColumnTitle); //列标题样式
	    HSSFCellStyle[] cellStyles = new HSSFCellStyle[cols]; //列单元格样式
	    
	    do{
	    	
		    // 创建Excel的工作sheet
		    HSSFSheet sheet = wb.createSheet("sheet"+(++sheets));
		    rows = 0;
		    
		    //表头
		    HSSFRow row = null;
		    HSSFCell cell = null;
		    if(title!=null){
			    row = sheet.createRow(rows++);
			    row.setHeightInPoints(20f);       //设定行的高度
			    cell = row.createCell(0);	    
			    CellRangeAddress cellRange = new CellRangeAddress(0, 0, 0, cols-1);
			    sheet.addMergedRegion(cellRange); // 合并单元格(startRow，endRow，startColumn，endColumn)
			    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			    cell.setCellStyle(headlineStyle);
			    cell.setCellValue(title);
		    }
		    
		    //列标题
		    row = sheet.createRow(rows++);
		    row.setHeightInPoints(20f);       //设定行的高度
		    for(int i=0; i<cols; i++){
			    cell = row.createCell(i);
			    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			    cell.setCellStyle(colTitleStyle);
			    cell.setCellValue(colDefs.get(i).getTitle());
		    }
		    
		    //数据
		    for(; dataList!=null && count< dataList.size(); ){		    	

		    	Object data = dataList.get(count++);
		    	if(data == null){
		    		continue;
		    	}
		    	
		    	row = sheet.createRow(rows++);
		    	
		    	for(int j=0; j<cols; j++){
			    	cell = row.createCell(j);
			    	
			    	String type = colDefs.get(j).getType();
			    	String property = colDefs.get(j).getProperty();
			    	Map<String, String> dict = colDefs.get(j).getDict();
			    	Object obj = null;
			    	if(property!=null && !"".equals(property)){
			    		try{
			    			obj = PropertyUtils.getProperty(data, property);
			    		}catch(org.apache.commons.beanutils.NestedNullException e){
			    			
			    		}
			    	}
			    	
			    	//设置单元格样式
			    	if(cellStyles[j]==null){
			    		cellStyles[j] = createCellStyle(colDefs.get(j), obj, wb, font);
			    	}
			    	cell.setCellStyle(cellStyles[j]);
			    	
			    	//设置值
			    	if(TYPE_SN.equals(type)){
			    		cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
			    		cell.setCellValue((count)+"");
			    	}else if(TYPE_CODE.equals(type)){
			    		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			    		if(obj==null){
			    			//cell.setCellValue("");
			    		}else if(dict!=null && dict.get(String.valueOf(obj))!=null){
			    			cell.setCellValue(dict.get(String.valueOf(obj)));
			    		}else{
			    			cell.setCellValue(String.valueOf(obj));
			    		}
			    	}else if(TYPE_TEXT.equals(type)){
			    		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			    		cell.setCellValue(obj==null?"":String.valueOf(obj));
			    	}else if(TYPE_NUMBERIC.equals(type)){
			    		if(obj==null){
			    			cell.setCellType(HSSFCell.CELL_TYPE_BLANK);
			    		}else{
			    			cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				    		try{
			    				cell.setCellValue(Double.parseDouble(String.valueOf(obj)));
			    			}catch(Exception e){
				    			cell.setCellValue(String.valueOf(obj));
			    			}
			    		}
			    	}else if(TYPE_DATETIME.equals(type) || TYPE_DATE.equals(type)){
			    		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
			    		if(obj instanceof Date){
		    				cell.setCellValue((Date)obj);
		    			}else if(obj instanceof Calendar){
		    				cell.setCellValue((Calendar)obj);
		    			}else{
		    				cell.setCellValue(obj==null?"":String.valueOf(obj));
		    			}
			    	}else{//未指定TYPE
			    		if(obj instanceof Number){
				    		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
				    		cell.setCellValue(Double.parseDouble(String.valueOf(obj)));
			    		}else if(obj instanceof Date || obj instanceof Calendar){
				    		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
			    			if(obj instanceof Date){
			    				cell.setCellValue((Date)obj);
			    			}
			    			cell.setCellValue((Calendar)obj);
			    		}else{//String
				    		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				    		cell.setCellValue(obj==null?"":String.valueOf(obj));
			    		}
			    	}
		    	}
		    	
		    	if(rows>=maxRows){
		    		break;
		    	}
		    }
		  
		    // 设置excel每列宽度
		    for(int i=0; i<cols; i++){
		    	if(colDefs.get(i).getWidth()>0){
		    		sheet.setColumnWidth(i, colDefs.get(i).getWidth());
		    	}else{
		    		sheet.autoSizeColumn(i);
		    	}
		    }
	    }while(dataList!=null && count<dataList.size());
	    
	    wb.write(os);
	}
	
	/**
	 * MS EXCEL 输出
	 * @param title 标题。null表示无标题行
	 * @param colDefs 列定义
	 * @param dataList 数据
	 * @param response
	 * @param filename 下载文件名
	 * @throws Exception
	 */
	public static void export(String title, List<ExcelColumnDefinition> colDefs, List<?> dataList, HttpServletResponse response, String filename)
	throws Exception{
		
		if(colDefs == null || colDefs.size() == 0 || response == null){
			throw new Exception("Invalid parameters");
		}
		
		String fname = MyStringUtils.isEmptyOrWhitespace(filename) ? 
				(MyStringUtils.isEmptyOrWhitespace(title) ? "list.xls" : title.trim()): filename.trim();
		
		if(!fname.toLowerCase().endsWith(".xls")){
			fname += ".xls";
		}
		
		response.reset();
		response.setContentType("application/x-msdownload");
		response.setHeader("content-disposition", "attachment; filename=" + URLEncoder.encode(fname,"utf-8"));
		OutputStream os = response.getOutputStream();
		export(title, colDefs, dataList, os);
		os.flush();
		os.close();
	}
	
	/**
	 * 根据列定义和数据创建单元格样式
	 * @param colDef
	 * @param data
	 * @param workbook
	 * @param font
	 * @return
	 * @throws Exception
	 */
	private static HSSFCellStyle createCellStyle(ExcelColumnDefinition colDef, Object data, HSSFWorkbook workbook, HSSFFont font)
	throws Exception{
		
		HSSFCellStyle style = null;
		
    	String type = colDef.getType();
    	String pattern = colDef.getPattern();
    	
    	if(TYPE_SN.equals(type)){
    		style = createTextCellStyle(workbook, font);
    	}else if(TYPE_CODE.equals(type)){
    		style = createTextCellStyle(workbook, font);
    	}else if(TYPE_TEXT.equals(type)){
    		style = createTextCellStyle(workbook, font);
    	}else if(TYPE_NUMBERIC.equals(type)){
    		if(pattern!=null && !"".equals(pattern)){
    			style = createCommonCellStyle(workbook, font);
    			style.setDataFormat(workbook.createDataFormat().getFormat(pattern));//格式
    		}else{
    			style = createNumericCellStyle(workbook, font);
    		}
    	}else if(TYPE_DATETIME.equals(type) || TYPE_DATE.equals(type)){
    		if(pattern!=null && !"".equals(pattern)){
    			style = createCommonCellStyle(workbook, font);
    			style.setDataFormat(workbook.createDataFormat().getFormat(pattern));//格式
    		}else{
	    		if(TYPE_DATETIME.equals(type)){
	    			style = createDatetimeCellStyle(workbook, font);
	    		}else{
	    			style = createDateCellStyle(workbook, font);
	    		}
    		}
    	}else if(data==null){
    		style = createTextCellStyle(workbook, font);
    	}else{
    		if(data instanceof Number){
    			style = createNumericCellStyle(workbook, font);
    		}else if(data instanceof Date || data instanceof Calendar){
    			style = createDatetimeCellStyle(workbook, font);
    		}else{//String
    			style = createTextCellStyle(workbook, font);
    		}
    	}
	    	
	    return style;
	}
	
	private static HSSFCellStyle createTitleCellStyle(HSSFWorkbook workbook, HSSFFont font){
	    HSSFCellStyle style = createCommonCellStyle(workbook, font);
	    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平对齐
	    style.setWrapText(false); //自动换行
	    return style;
	}
	
	private static HSSFCellStyle createDateCellStyle(HSSFWorkbook workbook, HSSFFont font){
	    HSSFCellStyle style = createCommonCellStyle(workbook, font);
	    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平对齐
	    style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd")); //格式
	    return style;
	}
	
	private static HSSFCellStyle createDatetimeCellStyle(HSSFWorkbook workbook, HSSFFont font){
	    HSSFCellStyle style = createCommonCellStyle(workbook, font);
	    style.setAlignment(HSSFCellStyle.ALIGN_CENTER); //水平对齐
	    style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-mm-dd hh:mm:ss")); //格式
	    return style;
	}
	
	private static HSSFCellStyle createTextCellStyle(HSSFWorkbook workbook, HSSFFont font){
	    HSSFCellStyle style = createCommonCellStyle(workbook, font);
	    style.setAlignment(HSSFCellStyle.ALIGN_LEFT); //水平对齐
	    style.setWrapText(true); //自动换行
	    return style;
	}
	
	private static HSSFCellStyle createNumericCellStyle(HSSFWorkbook workbook, HSSFFont font){
	    HSSFCellStyle style = createCommonCellStyle(workbook, font);
	    style.setAlignment(HSSFCellStyle.ALIGN_RIGHT); //水平对齐
	    //style.setDataFormat(workbook.createDataFormat().getFormat("#,##0.00"));//格式
	    return style;
	}
	
	private static HSSFCellStyle createCommonCellStyle(HSSFWorkbook workbook, HSSFFont font){
	    
	    // 创建单元格样式
	    HSSFCellStyle style = workbook.createCellStyle();
	    //垂直对齐
	    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
	    //填充
	    style.setFillForegroundColor(HSSFColor.BLACK.index);      //前景色
	    style.setFillBackgroundColor(HSSFColor.AUTOMATIC.index);  //背景色
	    style.setFillPattern(HSSFCellStyle.NO_FILL);	          //底纹图案
	    //边框
	    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
	    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
	    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
	    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
	    style.setTopBorderColor(HSSFColor.BLACK.index);
	    style.setRightBorderColor(HSSFColor.BLACK.index);
	    style.setBottomBorderColor(HSSFColor.BLACK.index);
	    style.setLeftBorderColor(HSSFColor.BLACK.index);
	    //字体
	    style.setFont(font);
	    
	    return style;
	}

}
